<?php
	// koneksi ke mysql
	require_once "../../config/koneksi.php";

	// menggunakan class phpExcelReader
	include "excel_reader2.php";

	// membaca file excel yang diupload
	$data = new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);

	// membaca jumlah baris dari data excel
	$baris = $data->rowcount($sheet_index=0);

	// nilai awal counter untuk jumlah data yang sukses dan yang gagal diimport
	$sukses = 0;
	$gagal = 0;

	// import data excel mulai baris ke-2 (karena baris pertama adalah nama kolom)
	for ($i=2; $i<=$baris; $i++){
		$nik = $data->val($i, 2);
		$id_kk = $data->val($i, 3);
		$nama = $data->val($i, 4);
		$jk = $data->val($i, 5);
		$tempat_lahir = $data->val($i, 6);
		$tgl_lahir = $data->val($i, 7);
		$id_agama = $data->val($i, 8);
		$agama = $data->val($i, 9);
		$id_pendidikan = $data->val($i, 10);
		$pendidikan = $data->val($i, 11);
		$id_pekerjaan = $data->val($i, 12);
		$pekerjaan = $data->val($i, 13);
		$id_status_nikah = $data->val($i, 14);
		$status_nikah = $data->val($i, 15);
		$id_hub_keluarga = $data->val($i, 16);
		$hub_keluarga = $data->val($i, 17);
		$kewarganegaraan = $data->val($i, 18);
		$nopas = $data->val($i, 19);
		$nokitas = $data->val($i, 20);
		$ayah = $data->val($i, 21);
		$ibu = $data->val($i, 22);
		$miskin = $data->val($i, 23);
		$bpjs = $data->val($i, 24);
		
		$query = "insert into penduduk (nik,id_kk,nama,jk,tempat_lahir,tgl_lahir,id_agama,id_pendidikan,id_pekerjaan,id_status,id_hub_keluarga,kewarganegaraan,nopaspor,nokitas,ayah,ibu,is_miskin,bpjs) values ('$nik','$id_kk','$nama','$jk','$tempat_lahir','$tgl_lahir','$id_agama','$id_pendidikan','$id_pekerjaan','$id_status_nikah','$id_hub_keluarga','$kewarganegaraan','$nopas','$nokitas','$ayah','$ibu','$miskin','$bpjs')";
		//echo $query;
		$hasil = mysql_query($query);
		
		if ($hasil){
			$sukses++;
		}
		else{
			$gagal++;
			$data_gagal[$gagal] = array(
				"nik" => "$nik",
				"id_kk" => "$id_kk",
				"nama" => "$nama",
				"jk" => "$jk",
				"tempat_lahir" => "$tempat_lahir",
				"tgl_lahir" => "$tgl_lahir",
				"id_agama" => "$id_agama",
				"agama" => "$agama",
				"id_pendidikan" => "$id_pendidikan",
				"pendidikan" => "$pendidikan",
				"id_pekerjaan" => "$id_pekerjaan",
				"pekerjaan" => "$pekerjaan",
				"id_status" => "$id_status_nikah",
				"status" => "$status_nikah",
				"id_hub_keluarga" => "$id_hub_keluarga",
				"hub_keluarga" => "$hub_keluarga",
				"kewarganegaraan" => "$kewarganegaraan",
				"nopaspor" => "$nopas",
				"nokitas" => "$nokitas",
				"ayah" => "$ayah",
				"ibu" => "$ibu",
				"miskin" => "$miskin",
				"bpjs" => "$bpjs"
			);
		}
	}
	
	
?>
<?php
	if($gagal > 0){
	  require_once('../../export/Worksheet.php');
	  require_once('../../export/Workbook.php');
	  // koneksi ke mysql
	  require_once('../../config/koneksi.php');

	  // function untuk membuat header file excel
	  function HeaderingExcel() {
		  $tgl = date("dmY");
		  header("Content-type: application/vnd.ms-excel");
		  header("Content-Disposition: attachment; filename=Import_Penduduk_Gagal.xls" );
		  header("Expires: 0");
		  header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
		  header("Pragma: public");
		  }

	  // membuat header file excel dan nama filenya
	  HeaderingExcel('Data Import Penduduk.xls');

	  // membuat workbook baru
	  $workbook = new Workbook("");
	  // membuat worksheet ke-1 (data laki-laki)
	  $worksheet1 =& $workbook->add_worksheet('Data Penduduk');

	  // setting format header tabel data
	  $format =& $workbook->add_format();
	  $format->set_align('vcenter');
	  $format->set_align('center');
	  $format->set_color('white');
	  $format->set_bold();
	  $format->set_italic();
	  $format->set_pattern();
	  $format->set_fg_color('green');
	  
	  // membuat header tabel dengan format
	  $worksheet1->set_row(0, 15);
	  $worksheet1->set_column(0, 1, 20);
	  $worksheet1->write_string(0, 1, "Sukses");
	  $worksheet1->set_column(0, 2, 20);
	  $worksheet1->write_number(0, 2, "$sukses");
	  
	  $worksheet1->set_row(1, 15);
	  $worksheet1->set_column(1, 1, 20);
	  $worksheet1->write_string(1, 1, "Gagal");
	  $worksheet1->set_column(1, 2, 20);
	  $worksheet1->write_number(1, 2, "$gagal");
	  
	  // membuat header tabel dengan format
	  $worksheet1->set_row(0, 15);
	  $worksheet1->set_column(0, 0, 5);
	  $worksheet1->write_string(3, 0, "NO", $format);
	  $worksheet1->set_column(3, 1, 20);
	  $worksheet1->write_string(3, 1, "NIK", $format);
	  $worksheet1->set_column(3, 2, 20);
	  $worksheet1->write_string(3, 2, "NOMER KK", $format);
	  $worksheet1->set_column(3, 3, 30);
	  $worksheet1->write_string(3, 3, "NAMA", $format);
	  $worksheet1->set_column(3, 4, 5);
	  $worksheet1->write_string(3, 4, "JK", $format);
	  $worksheet1->set_column(3, 5, 15);
	  $worksheet1->write_string(3, 5, "TEMPAT LAHIR", $format);
	  $worksheet1->set_column(3, 6, 15);
	  $worksheet1->write_string(3, 6, "TANGGAL LAHIR", $format);
	  $worksheet1->set_column(3, 7, 20);
	  $worksheet1->write_string(3, 7, "KODE AGAMA", $format);
	  $worksheet1->set_column(3, 8, 20);
	  $worksheet1->write_string(3, 8, "AGAMA", $format);
	  $worksheet1->set_column(3, 9, 20);
	  $worksheet1->write_string(3, 9, "KODE PENDDIDIKAN", $format);
	  $worksheet1->set_column(3, 10, 20);
	  $worksheet1->write_string(3, 10, "PENDIDIKAN", $format);
	  $worksheet1->set_column(3, 11, 20);
	  $worksheet1->write_string(3, 11, "KODE PEKERJAAN", $format);
	  $worksheet1->set_column(3, 12, 20);
	  $worksheet1->write_string(3, 12, "PEKERJAAN", $format);
	  $worksheet1->set_column(3, 13, 20);
	  $worksheet1->write_string(3, 13, "KODE STATUS NIKAH", $format);
	  $worksheet1->set_column(3, 14, 20);
	  $worksheet1->write_string(3, 14, "STATUS NIKAH", $format);
	  $worksheet1->set_column(3, 15, 20);
	  $worksheet1->write_string(3, 15, "KODE HUB. KELUARGA", $format);
	  $worksheet1->set_column(3, 16, 20);
	  $worksheet1->write_string(3, 16, "HUBUNGAN KELUARGA", $format);
	  $worksheet1->set_column(3, 17, 20);
	  $worksheet1->write_string(3, 17, "KEWARGANEGARAAN", $format);
	  $worksheet1->set_column(3, 18, 20);
	  $worksheet1->write_string(3, 18, "NOMER PASPOR", $format);
	  $worksheet1->set_column(3, 19, 20);
	  $worksheet1->write_string(3, 19, "NOMER KITAS", $format);
	  $worksheet1->set_column(3, 20, 30);
	  $worksheet1->write_string(3, 20, "AYAH", $format);
	  $worksheet1->set_column(3, 21, 30);
	  $worksheet1->write_string(3, 21, "IBU", $format);
	  $worksheet1->set_column(3, 22, 10);
	  $worksheet1->write_string(3, 22, "MISKIN", $format);
	  $worksheet1->set_column(3, 23, 10);
	  $worksheet1->write_string(3, 23, "BPJS", $format);

	  // menampilkan data mhasiswa laki-laki
	  $baris = 4;
	  for($i=1;$i<=$gagal;$i++){
			$no++;
			$worksheet1->write_number($baris, 0, $no);
			$worksheet1->write_string($baris, 1, $data_gagal[$no]['nik']);
			$worksheet1->write_string($baris, 2, $data_gagal[$no]['id_kk']);
			$worksheet1->write_string($baris, 3, $data_gagal[$no]['nama']);
			$worksheet1->write_string($baris, 4, $data_gagal[$no]['jk']);
			$worksheet1->write_string($baris, 5, $data_gagal[$no]['tempat_lahir']);
			$worksheet1->write_string($baris, 6, $data_gagal[$no]['tgl_lahir']);
			$worksheet1->write_string($baris, 7, $data_gagal[$no]['id_agama']);
			$worksheet1->write_string($baris, 8, $data_gagal[$no]['agama']);
			$worksheet1->write_string($baris, 9, $data_gagal[$no]['id_pendidikan']);
			$worksheet1->write_string($baris, 10, $data_gagal[$no]['pendidikan']);
			$worksheet1->write_string($baris, 11, $data_gagal[$no]['id_pekerjaan']);
			$worksheet1->write_string($baris, 12, $data_gagal[$no]['pekerjaan']);
			$worksheet1->write_string($baris, 13, $data_gagal[$no]['id_status']);
			$worksheet1->write_string($baris, 14, $data_gagal[$no]['status']);
			$worksheet1->write_string($baris, 15, $data_gagal[$no]['id_hub_keluarga']);
			$worksheet1->write_string($baris, 16, $data_gagal[$no]['hub_keluarga']);
			$worksheet1->write_string($baris, 17, $data_gagal[$no]['kewarganegaraan']);
			$worksheet1->write_string($baris, 18, $data_gagal[$no]['nopaspor']);
			$worksheet1->write_string($baris, 19, $data_gagal[$no]['nokitas']);
			$worksheet1->write_string($baris, 20, $data_gagal[$no]['ayah']);
			$worksheet1->write_string($baris, 21, $data_gagal[$no]['ibu']);
			$worksheet1->write_string($baris, 22, $data_gagal[$no]['miskin']);
			$worksheet1->write_string($baris, 23, $data_gagal[$no]['bpjs']);
			$baris++;
	  }

	  // membuat worksheet ke-2
	  $worksheet2 =& $workbook->add_worksheet('Data Agama');

	  // membuat header tabel
	  $worksheet2->set_row(0, 15);
	  $worksheet2->set_column(0, 0, 5);
	  $worksheet2->write_string(0, 0, "KODE", $format);
	  $worksheet2->set_column(0, 1, 30);
	  $worksheet2->write_string(0, 1, "AGAMA", $format);

	  // menampilkan data

	  $query = "SELECT * FROM agama order by id_agama";
	  $hasil = mysql_query($query);
	  $baris = 1;
	  while ($data = mysql_fetch_array($hasil))
	  {
			$worksheet2->write_number($baris, 0, $data['id_agama']);
			$worksheet2->write_string($baris, 1, $data['agama']);
			$baris++;
	  }
	  
	  // membuat worksheet ke-3
	  $worksheet3 =& $workbook->add_worksheet('Data Pendidikan');

	  // membuat header tabel
	  $worksheet3->set_row(0, 15);
	  $worksheet3->set_column(0, 0, 5);
	  $worksheet3->write_string(0, 0, "KODE", $format);
	  $worksheet3->set_column(0, 1, 30);
	  $worksheet3->write_string(0, 1, "PENDIDIKAN", $format);

	  // menampilkan data

	  $query = "SELECT * FROM pendidikan order by id_pendidikan";
	  $hasil = mysql_query($query);
	  $baris = 1;
	  while ($data = mysql_fetch_array($hasil))
	  {
			$worksheet3->write_number($baris, 0, $data['id_pendidikan']);
			$worksheet3->write_string($baris, 1, $data['pendidikan']);
			$baris++;
	  }

	  // membuat worksheet ke-4
	  $worksheet4 =& $workbook->add_worksheet('Data Pekerjaan');

	  // membuat header tabel
	  $worksheet4->set_row(0, 15);
	  $worksheet4->set_column(0, 0, 5);
	  $worksheet4->write_string(0, 0, "KODE", $format);
	  $worksheet4->set_column(0, 1, 30);
	  $worksheet4->write_string(0, 1, "PEKERJAAN", $format);

	  // menampilkan data

	  $query = "SELECT * FROM pekerjaan order by id_pekerjaan";
	  $hasil = mysql_query($query);
	  $baris = 1;
	  while ($data = mysql_fetch_array($hasil))
	  {
			$worksheet4->write_number($baris, 0, $data['id_pekerjaan']);
			$worksheet4->write_string($baris, 1, $data['pekerjaan']);
			$baris++;
	  }
	  
	  // membuat worksheet ke-4
	  $worksheet5 =& $workbook->add_worksheet('Data Status Nikah');

	  // membuat header tabel
	  $worksheet5->set_row(0, 15);
	  $worksheet5->set_column(0, 0, 5);
	  $worksheet5->write_string(0, 0, "KODE", $format);
	  $worksheet5->set_column(0, 1, 30);
	  $worksheet5->write_string(0, 1, "Status Nikah", $format);

	  // menampilkan data

	  $query = "SELECT * FROM status_nikah order by id_status";
	  $hasil = mysql_query($query);
	  $baris = 1;
	  while ($data = mysql_fetch_array($hasil))
	  {
			$worksheet5->write_number($baris, 0, $data['id_status']);
			$worksheet5->write_string($baris, 1, $data['status_nikah']);
			$baris++;
	  }
	  
	  // membuat worksheet ke-5
	  $worksheet6 =& $workbook->add_worksheet('Data Hubungan Keluarga');

	  // membuat header tabel
	  $worksheet6->set_row(0, 15);
	  $worksheet6->set_column(0, 0, 5);
	  $worksheet6->write_string(0, 0, "KODE", $format);
	  $worksheet6->set_column(0, 1, 30);
	  $worksheet6->write_string(0, 1, "Hubungan Keluarga", $format);

	  // menampilkan data

	  $query = "SELECT * FROM hub_keluarga order by id_hub_keluarga";
	  $hasil = mysql_query($query);
	  $baris = 1;
	  while ($data = mysql_fetch_array($hasil))
	  {
			$worksheet6->write_number($baris, 0, $data['id_hub_keluarga']);
			$worksheet6->write_string($baris, 1, $data['hub_keluarga']);
			$baris++;
	  }

	  $workbook->close();
	}else{
		echo "<h3>Proses import data selesai.</h3>";
		echo "<p>Jumlah data yang sukses diimport : ".$sukses."<br>";
		echo "Jumlah data yang gagal diimport : ".$gagal."</p>";
	}
?>